{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "i7EUnXsZhAGF"
  },
  "source": [
   "# Training and Deploying a Prediction model for Real-time Inference\n",
   "\n",
   "## Learning objectives\n",
   "\n",
   "1. Use Python & SQL to query the public data in BigQuery.\n",
   "2. Prepare the data for modeling.\n",
   "3. Train a classification model using BigQuery ML.\n",
   "4. Inspect the model on Vertex AI Model Registry.\n",
   "5. Deploy the model to an endpoint.\n",
   "6. Make online predictions to the endpoint."
    ]
   },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tvgnzT1CKxrO"
   },
   "source": [
    "## Overview\n",
    "\n",
    "This notebook is aimed at data analysts and data scientists who have data in BigQuery, want to train a model using BigQuery ML, register the model to Vertex AI Model Registry, and deploy it to an endpoint for real-time prediction. \n",
    "\n",
    "In this notebook, you will learn how to train and deploy a churn prediction model for real-time inference, with the data in BigQuery and model trained using BigQuery ML, registered to Vertex AI Model Registry, and deployed to an endpoint on Vertex AI for online predictions.\n",
    "\n",
    "### Dataset\n",
    "\n",
    "The dataset, <a href=\"https://console.cloud.google.com/bigquery?project=bigquery-public-data&d=ga4_obfuscated_sample_ecommerce&p=bigquery-public-data&page=dataset\" target=\"_blank\">available publicly on BigQuery</a>, comes from obfuscated <a href=\"https://support.google.com/analytics/answer/10937659\" target=\"_blank\">Google Analytics 4 data</a> from the <a href=\"https://shop.googlemerchandisestore.com/\" target=\"_blank\">Google Merchandise Store</a>).\n",
    "\n",
    "Each learning objective will correspond to a __#TODO__ in the [student lab notebook](../labs/bqml-online-prediction.ipynb) -- try to complete that notebook first before reviewing this solution notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "i7EUnXsZhAGF"
   },
   "source": [
    "### Install additional packages\n",
    "\n",
    "Install the following packages required to execute this notebook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "2b4ef9b72d43"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[33m  WARNING: The script tb-gcp-uploader is installed in '/home/jupyter/.local/bin' which is not on PATH.\n",
      "  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.\u001b[0m\u001b[33m\n",
      "\u001b[0m\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
      "google-cloud-recommendations-ai 0.2.0 requires google-api-core[grpc]<2.0.0dev,>=1.22.2, but you have google-api-core 2.8.1 which is incompatible.\n",
      "apache-beam 2.40.0 requires dill<0.3.2,>=0.3.1.1, but you have dill 0.3.5.1 which is incompatible.\n",
      "apache-beam 2.40.0 requires pyarrow<8.0.0,>=0.15.1, but you have pyarrow 9.0.0 which is incompatible.\u001b[0m\u001b[31m\n",
      "\u001b[0m"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "# The Vertex AI Workbench Notebook product has specific requirements\n",
    "IS_WORKBENCH_NOTEBOOK = os.getenv(\"DL_ANACONDA_HOME\")\n",
    "IS_USER_MANAGED_WORKBENCH_NOTEBOOK = os.path.exists(\n",
    "    \"/opt/deeplearning/metadata/env_version\"\n",
    ")\n",
    "\n",
    "# Vertex AI Notebook requires dependencies to be installed with '--user'\n",
    "USER_FLAG = \"\"\n",
    "if IS_WORKBENCH_NOTEBOOK:\n",
    "    USER_FLAG = \"--user\"\n",
    "\n",
    "! pip3 install --upgrade google-cloud-aiplatform {USER_FLAG} -q google-cloud-bigquery db-dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hhq5zEbGg0XX"
   },
   "source": [
    "### Restart the kernel\n",
    "\n",
    "After you install the additional packages, you need to restart the notebook kernel so it can find the packages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "EzrelQZ22IZj"
   },
   "outputs": [],
   "source": [
    "# Automatically restart kernel after installs\n",
    "import os\n",
    "\n",
    "if not os.getenv(\"IS_TESTING\"):\n",
    "    # Automatically restart kernel after installs\n",
    "    import IPython\n",
    "\n",
    "    app = IPython.Application.instance()\n",
    "    app.kernel.do_shutdown(True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lWEdiXsJg0XY"
   },
   "source": [
    "## Before you begin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WReHDGG5g0XY"
   },
   "source": [
    "#### Set your project ID\n",
    "\n",
    "**If you don't know your project ID**, you may be able to get your project ID using `gcloud`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "oM1iC_MfAts1"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Project ID:  qwiklabs-gcp-04-146a9155ea7b\n"
     ]
    }
   ],
   "source": [
    "PROJECT_ID = \"[your-project-id]\" # Replace with your Project ID\n",
    "\n",
    "# Get your Google Cloud project ID from gcloud\n",
    "import os\n",
    "\n",
    "if not os.getenv(\"IS_TESTING\"):\n",
    "    shell_output = !gcloud config list --format 'value(core.project)' 2>/dev/null\n",
    "    PROJECT_ID = shell_output[0]\n",
    "    print(\"Project ID: \", PROJECT_ID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "region"
   },
   "source": [
    "#### Region\n",
    "\n",
    "You can also change the `REGION` variable, which is used for operations\n",
    "throughout the rest of this notebook.  Below are regions supported for Vertex AI. We recommend that you choose the region closest to you.\n",
    "\n",
    "- Americas: `us-central1`\n",
    "- Europe: `europe-west4`\n",
    "- Asia Pacific: `asia-east1`\n",
    "\n",
    "You might not be able to use a multi-regional bucket for training with Vertex AI. Not all regions provide support for all Vertex AI services.\n",
    "\n",
    "Learn more about <a href=\"https://cloud.google.com/vertex-ai/docs/general/locations\" target=\"_blank\">Vertex AI regions</a>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "region"
   },
   "outputs": [],
   "source": [
    "REGION = \"[your-region]\"  # Replace with your region\n",
    "\n",
    "if REGION == \"[your-region]\":\n",
    "    REGION = \"us-central1\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "06571eb4063b"
   },
   "source": [
    "#### UUID\n",
    "\n",
    "If you are in a live tutorial session, you might be using a shared test account or project. To avoid name collisions between users on resources created, you create a uuid for each instance session, and append it onto the name of resources you create in this tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "697568e92bd6"
   },
   "outputs": [],
   "source": [
    "import random\n",
    "import string\n",
    "\n",
    "\n",
    "# Generate a uuid of a specifed length(default=8)\n",
    "def generate_uuid(length: int = 8) -> str:\n",
    "    return \"\".join(random.choices(string.ascii_lowercase + string.digits, k=length))\n",
    "\n",
    "\n",
    "UUID = generate_uuid()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XoEqT2Y4DJmf"
   },
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "pRUOFELefqf1"
   },
   "outputs": [],
   "source": [
    "from typing import Union\n",
    "\n",
    "import google.cloud.aiplatform as vertex_ai\n",
    "import pandas as pd\n",
    "from google.cloud import bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "init_aip:mbsdk,all"
   },
   "source": [
    "### Initialize Vertex AI and BigQuery SDKs for Python\n",
    "\n",
    "Initialize the Vertex AI SDK for Python for your project and corresponding bucket."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "init_aip:mbsdk,all"
   },
   "outputs": [],
   "source": [
    "vertex_ai.init(project=PROJECT_ID, location=REGION)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "83859376c893"
   },
   "source": [
    "Create the BigQuery client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "id": "0ab485806b17"
   },
   "outputs": [],
   "source": [
    "# TODO 1\n",
    "# Create the BigQuery client w.r.t your Project ID\n",
    "bq_client = bigquery.Client(project=PROJECT_ID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f94734ac9312"
   },
   "source": [
    "Use a helper function for sending queries to BigQuery."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "e364dab1d353"
   },
   "outputs": [],
   "source": [
    "# Wrapper to use BigQuery client to run query/job, return job ID or result as DF\n",
    "def run_bq_query(sql: str) -> Union[str, pd.DataFrame]:\n",
    "    \"\"\"\n",
    "    Input: SQL query, as a string, to execute in BigQuery\n",
    "    Returns the query results as a pandas DataFrame, or error, if any\n",
    "    \"\"\"\n",
    "\n",
    "    # Try dry run before executing query to catch any errors\n",
    "    job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)\n",
    "    bq_client.query(sql, job_config=job_config)\n",
    "\n",
    "    # If dry run succeeds without errors, proceed to run query\n",
    "    job_config = bigquery.QueryJobConfig()\n",
    "    client_result = bq_client.query(sql, job_config=job_config)\n",
    "\n",
    "    job_id = client_result.job_id\n",
    "\n",
    "    # Wait for query/job to finish running. then get & return data frame\n",
    "    df = client_result.result().to_arrow().to_pandas()\n",
    "    print(f\"Finished job_id: {job_id}\")\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "a4a686de97f5"
   },
   "source": [
    "## BigQuery ML introduction\n",
    "\n",
    "BigQuery ML (BQML) provides the capability to train ML tabular models, such as classification, regression, forecasting, and matrix factorization, in BigQuery using SQL syntax directly. BigQuery ML uses the scalable infrastructure of BigQuery ML so you don't need to set up additional infrastructure for training or batch serving.\n",
    "\n",
    "Learn more about <a href=\"https://cloud.google.com/bigquery-ml/docs\" target=\"_blank\">BigQuery ML documentation</a>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "088e3b9577b3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE SCHEMA IF NOT EXISTS ga4_churnprediction_9amu5eqp\n",
      "Finished job_id: 46d9018a-2b98-4427-b7e8-f46a00783d62\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "BQ_DATASET_NAME = f\"ga4_churnprediction_{UUID}\"\n",
    "\n",
    "sql_create_dataset = f\"\"\"CREATE SCHEMA IF NOT EXISTS {BQ_DATASET_NAME}\"\"\"\n",
    "\n",
    "print(sql_create_dataset)\n",
    "\n",
    "run_bq_query(sql_create_dataset)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "13b6ce9f8d8b"
   },
   "source": [
    "### Inspect the pre-processed Google Analytics 4 data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "49dd00d5fbe5"
   },
   "source": [
    "Inpect data that has been pre-processed from <a href=\"https://support.google.com/analytics/answer/10937659\" target=\"_blank\">Google Analytics 4 data from the Google Merchandise Store</a> so that it can be used for classification. For more information on how this data was prepared, read <a href=\"https://cloud.google.com/blog/topics/developers-practitioners/churn-prediction-game-developers-using-google-analytics-4-ga4-and-bigquery-ml\" target=\"_blank\">this blog post</a>.\n",
    "\n",
    "As seen below, each row represents a single user, and the columns represent their demographic features, their aggregated behavioral features in the first 24 hours of visiting the Google Merchandise Store, and the label (whether the user churned or returned any time after the first 24 hours)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "0916b03610bd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: c0fbd61a-9da5-46a1-bc7a-51b3f7901fde\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_pseudo_id</th>\n",
       "      <th>country</th>\n",
       "      <th>operating_system</th>\n",
       "      <th>language</th>\n",
       "      <th>cnt_user_engagement</th>\n",
       "      <th>cnt_page_view</th>\n",
       "      <th>cnt_view_item</th>\n",
       "      <th>cnt_view_promotion</th>\n",
       "      <th>cnt_select_promotion</th>\n",
       "      <th>cnt_add_to_cart</th>\n",
       "      <th>cnt_begin_checkout</th>\n",
       "      <th>cnt_add_shipping_info</th>\n",
       "      <th>cnt_add_payment_info</th>\n",
       "      <th>cnt_purchase</th>\n",
       "      <th>user_first_engagement</th>\n",
       "      <th>month</th>\n",
       "      <th>julianday</th>\n",
       "      <th>dayofweek</th>\n",
       "      <th>churned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7666337.2408476627</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 19:23:47.339545+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44740556.1615757380</td>\n",
       "      <td>Canada</td>\n",
       "      <td>Macintosh</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 11:42:53.072756+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91028872.0097105946</td>\n",
       "      <td>United States</td>\n",
       "      <td>Windows</td>\n",
       "      <td>en-us</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 19:07:53.669542+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63655374.9348413503</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>es-es</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:03:29.281546+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10115718.4867090359</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>None</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 18:32:29.296973+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>2304235.4728947355</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 16:39:41.910818+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>4902634.1944567521</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:08:53.020090+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>6385315.9680167296</td>\n",
       "      <td>Russia</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 11:08:32.462414+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>36665674.3318230125</td>\n",
       "      <td>Spain</td>\n",
       "      <td>&lt;Other&gt;</td>\n",
       "      <td>en-us</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 03:06:33.897251+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>5419406949.1676603341</td>\n",
       "      <td>India</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:14:25.747326+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           user_pseudo_id        country operating_system language  \\\n",
       "0      7666337.2408476627  United States              Web    en-us   \n",
       "1     44740556.1615757380         Canada        Macintosh     None   \n",
       "2     91028872.0097105946  United States          Windows    en-us   \n",
       "3     63655374.9348413503  United States              Web    es-es   \n",
       "4     10115718.4867090359  United States              Web     None   \n",
       "..                    ...            ...              ...      ...   \n",
       "95     2304235.4728947355  United States              Web    en-us   \n",
       "96     4902634.1944567521        Germany              Web    en-us   \n",
       "97     6385315.9680167296         Russia              Web    en-us   \n",
       "98    36665674.3318230125          Spain          <Other>    en-us   \n",
       "99  5419406949.1676603341          India              Web    en-us   \n",
       "\n",
       "    cnt_user_engagement  cnt_page_view  cnt_view_item  cnt_view_promotion  \\\n",
       "0                     5              6              2                   0   \n",
       "1                     6              7              2                   0   \n",
       "2                     2              3              0                   1   \n",
       "3                     8             10              6                   3   \n",
       "4                    15             12              9                   0   \n",
       "..                  ...            ...            ...                 ...   \n",
       "95                    1              2              0                   0   \n",
       "96                    1              3              0                   1   \n",
       "97                    2              5              1                   2   \n",
       "98                    2              4              0                   0   \n",
       "99                    3              4              1                   0   \n",
       "\n",
       "    cnt_select_promotion  cnt_add_to_cart  cnt_begin_checkout  \\\n",
       "0                      0                0                   0   \n",
       "1                      0                0                   0   \n",
       "2                      0                0                   0   \n",
       "3                      0                0                   0   \n",
       "4                      0                0                   0   \n",
       "..                   ...              ...                 ...   \n",
       "95                     0                0                   0   \n",
       "96                     0                0                   0   \n",
       "97                     1                0                   0   \n",
       "98                     0                0                   0   \n",
       "99                     0                0                   0   \n",
       "\n",
       "    cnt_add_shipping_info  cnt_add_payment_info  cnt_purchase  \\\n",
       "0                       0                     0             0   \n",
       "1                       0                     0             0   \n",
       "2                       0                     0             0   \n",
       "3                       0                     0             0   \n",
       "4                       0                     0             0   \n",
       "..                    ...                   ...           ...   \n",
       "95                      0                     0             0   \n",
       "96                      0                     0             0   \n",
       "97                      0                     0             0   \n",
       "98                      0                     0             0   \n",
       "99                      0                     0             0   \n",
       "\n",
       "              user_first_engagement  month  julianday  dayofweek  churned  \n",
       "0  2021-01-01 19:23:47.339545+00:00      1          1          6        1  \n",
       "1  2021-01-01 11:42:53.072756+00:00      1          1          6        1  \n",
       "2  2021-01-01 19:07:53.669542+00:00      1          1          6        1  \n",
       "3  2021-01-01 12:03:29.281546+00:00      1          1          6        0  \n",
       "4  2021-01-01 18:32:29.296973+00:00      1          1          6        1  \n",
       "..                              ...    ...        ...        ...      ...  \n",
       "95 2021-01-01 16:39:41.910818+00:00      1          1          6        0  \n",
       "96 2021-01-01 12:08:53.020090+00:00      1          1          6        0  \n",
       "97 2021-01-01 11:08:32.462414+00:00      1          1          6        1  \n",
       "98 2021-01-01 03:06:33.897251+00:00      1          1          6        1  \n",
       "99 2021-01-01 12:14:25.747326+00:00      1          1          6        0  \n",
       "\n",
       "[100 rows x 19 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_inspect = \"\"\"\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    `bqmlpublic.demo_ga4churnprediction.training_data`\n",
    "LIMIT\n",
    "    100\n",
    "\"\"\"\n",
    "run_bq_query(sql_inspect)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "02f304053600"
   },
   "source": [
    "### Train a classification model using BigQuery ML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "566f3395f20b"
   },
   "source": [
    "The query below trains a logistic regression model using BigQuery ML. BigQuery resources are used to train the model.\n",
    "\n",
    "In the `OPTIONS` parameter:\n",
    "* with `model_registry=\"vertex_ai\"`, the BigQuery ML model will automatically be <a href=\"https://cloud.google.com/vertex-ai/docs/model-registry/model-registry-bqml\" target=\"_blank\">registered to Vertex AI Model Registry</a>, which enables you to view all of your registered models and its versions on Google Cloud in one place.\n",
    "\n",
    "* `vertex_ai_model_version_aliases allows you to set aliases to help you keep track of your model version (<a href=\"https://cloud.google.com/vertex-ai/docs/model-registry/model-alias\" target=\"_blank\">documentation</a>)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "414c45011c1f"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE OR REPLACE MODEL ga4_churnprediction_9amu5eqp.bqml_model_churn_9amu5eqp    \n",
      "OPTIONS(\n",
      "  MODEL_TYPE=\"LOGISTIC_REG\",\n",
      "  input_label_cols=[\"churned\"],\n",
      "  model_registry=\"vertex_ai\",\n",
      "  vertex_ai_model_version_aliases=['logistic_reg', 'experimental']\n",
      ") AS\n",
      "\n",
      "SELECT\n",
      "  * EXCEPT(user_first_engagement, user_pseudo_id)\n",
      "FROM\n",
      "  bqmlpublic.demo_ga4churnprediction.training_data\n",
      "\n",
      "Finished job_id: 89999ef7-8688-49c1-9ebf-3752369914b5\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO 2\n",
    "# this cell may take ~1 min to run\n",
    "# Create a classification model\n",
    "\n",
    "BQML_MODEL_NAME = f\"bqml_model_churn_{UUID}\"\n",
    "\n",
    "sql_train_model_bqml = f\"\"\"\n",
    "CREATE OR REPLACE MODEL {BQ_DATASET_NAME}.{BQML_MODEL_NAME}    \n",
    "OPTIONS(\n",
    "  MODEL_TYPE=\"LOGISTIC_REG\",\n",
    "  input_label_cols=[\"churned\"],\n",
    "  model_registry=\"vertex_ai\",\n",
    "  vertex_ai_model_version_aliases=['logistic_reg', 'experimental']\n",
    ") AS\n",
    "\n",
    "SELECT\n",
    "  * EXCEPT(user_first_engagement, user_pseudo_id)\n",
    "FROM\n",
    "  bqmlpublic.demo_ga4churnprediction.training_data\n",
    "\"\"\"\n",
    "\n",
    "print(sql_train_model_bqml)\n",
    "\n",
    "run_bq_query(sql_train_model_bqml)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "a90e98c72a05"
   },
   "source": [
    "### Model evaluation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2aaaae772f67"
   },
   "source": [
    "With the model created, you can now evaluate the logistic regression model. Behind the scenes, BigQuery ML automatically <a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create#data_split_method\" target=\"_blank\">split the data</a>, which makes it easier to quickly train and evaluate models."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "a1f8ac93d570"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT\n",
      "  *\n",
      "FROM\n",
      "  ML.EVALUATE(MODEL ga4_churnprediction_9amu5eqp.bqml_model_churn_9amu5eqp)\n",
      "\n",
      "Finished job_id: 908795b4-8b83-4183-a5bf-d5e967ce4a98\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precision</th>\n",
       "      <th>recall</th>\n",
       "      <th>accuracy</th>\n",
       "      <th>f1_score</th>\n",
       "      <th>log_loss</th>\n",
       "      <th>roc_auc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.571924</td>\n",
       "      <td>0.995775</td>\n",
       "      <td>0.571083</td>\n",
       "      <td>0.726552</td>\n",
       "      <td>0.671342</td>\n",
       "      <td>0.602277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   precision    recall  accuracy  f1_score  log_loss   roc_auc\n",
       "0   0.571924  0.995775  0.571083  0.726552  0.671342  0.602277"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_evaluate_model = f\"\"\"\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL {BQ_DATASET_NAME}.{BQML_MODEL_NAME})\n",
    "\"\"\"\n",
    "\n",
    "print(sql_evaluate_model)\n",
    "\n",
    "run_bq_query(sql_evaluate_model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d9f807a50f38"
   },
   "source": [
    "These metrics help you understand the performance of the model. \n",
    "\n",
    "There are various metrics for logistic regression and other model types (full list of metrics can be found in the <a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate#mlevaluate_output\" target=\"_blank\">documentation</a>)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7e806ebc48a2"
   },
   "source": [
    "### Batch prediction (with Explainable AI)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d31605829283"
   },
   "source": [
    "Make a batch prediction in BigQuery ML on the original training data to check the probability of churn for each of the users, as seen in the `probability` column, with the predicted label under the `predicted_churn` column.\n",
    "\n",
    "<a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-explain-predict\" target=\"_blank\">ML.EXPLAIN_PREDICT</a> has built-in <a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-xai-overview\" target=\"_blank\">Explainable AI</a>. This allows you to see the top contributing features to each prediction and interpret how it was computed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "id": "2d500fdbfb44"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT\n",
      "  *\n",
      "FROM\n",
      "  ML.EXPLAIN_PREDICT(MODEL ga4_churnprediction_9amu5eqp.bqml_model_churn_9amu5eqp,\n",
      "    (SELECT * FROM bqmlpublic.demo_ga4churnprediction.training_data LIMIT 100)\n",
      "    )\n",
      "\n",
      "Finished job_id: 1007d132-d8f5-47e3-8a4f-3f5da72a383d\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>predicted_churned</th>\n",
       "      <th>probability</th>\n",
       "      <th>top_feature_attributions</th>\n",
       "      <th>baseline_prediction_value</th>\n",
       "      <th>prediction_value</th>\n",
       "      <th>approximation_error</th>\n",
       "      <th>user_pseudo_id</th>\n",
       "      <th>country</th>\n",
       "      <th>operating_system</th>\n",
       "      <th>language</th>\n",
       "      <th>...</th>\n",
       "      <th>cnt_add_to_cart</th>\n",
       "      <th>cnt_begin_checkout</th>\n",
       "      <th>cnt_add_shipping_info</th>\n",
       "      <th>cnt_add_payment_info</th>\n",
       "      <th>cnt_purchase</th>\n",
       "      <th>user_first_engagement</th>\n",
       "      <th>month</th>\n",
       "      <th>julianday</th>\n",
       "      <th>dayofweek</th>\n",
       "      <th>churned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 19:23:47.339545+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0.564491</td>\n",
       "      <td>[{'feature': 'month', 'attribution': 0.0808542...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.259407</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44740556.1615757380</td>\n",
       "      <td>Canada</td>\n",
       "      <td>Macintosh</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 11:42:53.072756+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0.591651</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.370796</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91028872.0097105946</td>\n",
       "      <td>United States</td>\n",
       "      <td>Windows</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 19:07:53.669542+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0.612900</td>\n",
       "      <td>[{'feature': 'month', 'attribution': 0.0808542...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.459517</td>\n",
       "      <td>0.0</td>\n",
       "      <td>63655374.9348413503</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>es-es</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:03:29.281546+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0.595990</td>\n",
       "      <td>[{'feature': 'month', 'attribution': 0.0808542...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.388785</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10115718.4867090359</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 18:32:29.296973+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>1</td>\n",
       "      <td>0.582265</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.332077</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2304235.4728947355</td>\n",
       "      <td>United States</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 16:39:41.910818+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1</td>\n",
       "      <td>0.595478</td>\n",
       "      <td>[{'feature': 'country', 'attribution': 0.09822...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.386659</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4902634.1944567521</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:08:53.020090+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>1</td>\n",
       "      <td>0.594728</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.383546</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6385315.9680167296</td>\n",
       "      <td>Russia</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 11:08:32.462414+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>1</td>\n",
       "      <td>0.578454</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.316432</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36665674.3318230125</td>\n",
       "      <td>Spain</td>\n",
       "      <td>&lt;Other&gt;</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 03:06:33.897251+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>1</td>\n",
       "      <td>0.584227</td>\n",
       "      <td>[{'feature': 'language', 'attribution': 0.0975...</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.340151</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5419406949.1676603341</td>\n",
       "      <td>India</td>\n",
       "      <td>Web</td>\n",
       "      <td>en-us</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-01 12:14:25.747326+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    predicted_churned  probability  \\\n",
       "0                   1     0.590461   \n",
       "1                   1     0.564491   \n",
       "2                   1     0.591651   \n",
       "3                   1     0.612900   \n",
       "4                   1     0.595990   \n",
       "..                ...          ...   \n",
       "95                  1     0.582265   \n",
       "96                  1     0.595478   \n",
       "97                  1     0.594728   \n",
       "98                  1     0.578454   \n",
       "99                  1     0.584227   \n",
       "\n",
       "                             top_feature_attributions  \\\n",
       "0   [{'feature': 'language', 'attribution': 0.0975...   \n",
       "1   [{'feature': 'month', 'attribution': 0.0808542...   \n",
       "2   [{'feature': 'language', 'attribution': 0.0975...   \n",
       "3   [{'feature': 'month', 'attribution': 0.0808542...   \n",
       "4   [{'feature': 'month', 'attribution': 0.0808542...   \n",
       "..                                                ...   \n",
       "95  [{'feature': 'language', 'attribution': 0.0975...   \n",
       "96  [{'feature': 'country', 'attribution': 0.09822...   \n",
       "97  [{'feature': 'language', 'attribution': 0.0975...   \n",
       "98  [{'feature': 'language', 'attribution': 0.0975...   \n",
       "99  [{'feature': 'language', 'attribution': 0.0975...   \n",
       "\n",
       "    baseline_prediction_value  prediction_value  approximation_error  \\\n",
       "0                    0.069157          0.365871                  0.0   \n",
       "1                    0.069157          0.259407                  0.0   \n",
       "2                    0.069157          0.370796                  0.0   \n",
       "3                    0.069157          0.459517                  0.0   \n",
       "4                    0.069157          0.388785                  0.0   \n",
       "..                        ...               ...                  ...   \n",
       "95                   0.069157          0.332077                  0.0   \n",
       "96                   0.069157          0.386659                  0.0   \n",
       "97                   0.069157          0.383546                  0.0   \n",
       "98                   0.069157          0.316432                  0.0   \n",
       "99                   0.069157          0.340151                  0.0   \n",
       "\n",
       "           user_pseudo_id        country operating_system language  ...  \\\n",
       "0      7666337.2408476627  United States              Web    en-us  ...   \n",
       "1     44740556.1615757380         Canada        Macintosh     None  ...   \n",
       "2     91028872.0097105946  United States          Windows    en-us  ...   \n",
       "3     63655374.9348413503  United States              Web    es-es  ...   \n",
       "4     10115718.4867090359  United States              Web     None  ...   \n",
       "..                    ...            ...              ...      ...  ...   \n",
       "95     2304235.4728947355  United States              Web    en-us  ...   \n",
       "96     4902634.1944567521        Germany              Web    en-us  ...   \n",
       "97     6385315.9680167296         Russia              Web    en-us  ...   \n",
       "98    36665674.3318230125          Spain          <Other>    en-us  ...   \n",
       "99  5419406949.1676603341          India              Web    en-us  ...   \n",
       "\n",
       "    cnt_add_to_cart  cnt_begin_checkout  cnt_add_shipping_info  \\\n",
       "0                 0                   0                      0   \n",
       "1                 0                   0                      0   \n",
       "2                 0                   0                      0   \n",
       "3                 0                   0                      0   \n",
       "4                 0                   0                      0   \n",
       "..              ...                 ...                    ...   \n",
       "95                0                   0                      0   \n",
       "96                0                   0                      0   \n",
       "97                0                   0                      0   \n",
       "98                0                   0                      0   \n",
       "99                0                   0                      0   \n",
       "\n",
       "    cnt_add_payment_info  cnt_purchase            user_first_engagement  \\\n",
       "0                      0             0 2021-01-01 19:23:47.339545+00:00   \n",
       "1                      0             0 2021-01-01 11:42:53.072756+00:00   \n",
       "2                      0             0 2021-01-01 19:07:53.669542+00:00   \n",
       "3                      0             0 2021-01-01 12:03:29.281546+00:00   \n",
       "4                      0             0 2021-01-01 18:32:29.296973+00:00   \n",
       "..                   ...           ...                              ...   \n",
       "95                     0             0 2021-01-01 16:39:41.910818+00:00   \n",
       "96                     0             0 2021-01-01 12:08:53.020090+00:00   \n",
       "97                     0             0 2021-01-01 11:08:32.462414+00:00   \n",
       "98                     0             0 2021-01-01 03:06:33.897251+00:00   \n",
       "99                     0             0 2021-01-01 12:14:25.747326+00:00   \n",
       "\n",
       "    month  julianday  dayofweek  churned  \n",
       "0       1          1          6        1  \n",
       "1       1          1          6        1  \n",
       "2       1          1          6        1  \n",
       "3       1          1          6        0  \n",
       "4       1          1          6        1  \n",
       "..    ...        ...        ...      ...  \n",
       "95      1          1          6        0  \n",
       "96      1          1          6        0  \n",
       "97      1          1          6        1  \n",
       "98      1          1          6        1  \n",
       "99      1          1          6        0  \n",
       "\n",
       "[100 rows x 25 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_explain_predict = f\"\"\"\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EXPLAIN_PREDICT(MODEL {BQ_DATASET_NAME}.{BQML_MODEL_NAME},\n",
    "    (SELECT * FROM bqmlpublic.demo_ga4churnprediction.training_data LIMIT 100)\n",
    "    )\n",
    "\"\"\"\n",
    "\n",
    "print(sql_explain_predict)\n",
    "\n",
    "run_bq_query(sql_explain_predict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fa1f96c0f452"
   },
   "source": [
    "Since the `top_feature_attributions` is a nested column, you can unnest the array (<a href=\"https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays\" target=\"_blank\">documentation</a>) into separate rows for each of the features. In other words, since ML.EXPLAIN_PREDICT provides the top 5 most important features, using `UNNEST` results in 5 rows per prediction:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "278b3441084b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT\n",
      "  tfa.*,\n",
      "  predicted_churned,\n",
      "  probability,\n",
      "  baseline_prediction_value,\n",
      "  prediction_value,\n",
      "  approximation_error,\n",
      "  user_pseudo_id\n",
      "FROM\n",
      "  ML.EXPLAIN_PREDICT(MODEL ga4_churnprediction_9amu5eqp.bqml_model_churn_9amu5eqp,\n",
      "    (SELECT * FROM bqmlpublic.demo_ga4churnprediction.training_data LIMIT 100)\n",
      "    ),\n",
      "  UNNEST(top_feature_attributions) as tfa\n",
      "WHERE\n",
      "  user_pseudo_id = \"7666337.2408476627\"\n",
      "\n",
      "Finished job_id: ecc6b6a6-c042-43c0-afee-ef2a92048902\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>feature</th>\n",
       "      <th>attribution</th>\n",
       "      <th>predicted_churned</th>\n",
       "      <th>probability</th>\n",
       "      <th>baseline_prediction_value</th>\n",
       "      <th>prediction_value</th>\n",
       "      <th>approximation_error</th>\n",
       "      <th>user_pseudo_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>language</td>\n",
       "      <td>0.097598</td>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>month</td>\n",
       "      <td>0.080854</td>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>julianday</td>\n",
       "      <td>0.080515</td>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>operating_system</td>\n",
       "      <td>0.075108</td>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>country</td>\n",
       "      <td>0.074942</td>\n",
       "      <td>1</td>\n",
       "      <td>0.590461</td>\n",
       "      <td>0.069157</td>\n",
       "      <td>0.365871</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7666337.2408476627</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            feature  attribution  predicted_churned  probability  \\\n",
       "0          language     0.097598                  1     0.590461   \n",
       "1             month     0.080854                  1     0.590461   \n",
       "2         julianday     0.080515                  1     0.590461   \n",
       "3  operating_system     0.075108                  1     0.590461   \n",
       "4           country     0.074942                  1     0.590461   \n",
       "\n",
       "   baseline_prediction_value  prediction_value  approximation_error  \\\n",
       "0                   0.069157          0.365871                  0.0   \n",
       "1                   0.069157          0.365871                  0.0   \n",
       "2                   0.069157          0.365871                  0.0   \n",
       "3                   0.069157          0.365871                  0.0   \n",
       "4                   0.069157          0.365871                  0.0   \n",
       "\n",
       "       user_pseudo_id  \n",
       "0  7666337.2408476627  \n",
       "1  7666337.2408476627  \n",
       "2  7666337.2408476627  \n",
       "3  7666337.2408476627  \n",
       "4  7666337.2408476627  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_explain_predict = f\"\"\"\n",
    "SELECT\n",
    "  tfa.*,\n",
    "  predicted_churned,\n",
    "  probability,\n",
    "  baseline_prediction_value,\n",
    "  prediction_value,\n",
    "  approximation_error,\n",
    "  user_pseudo_id\n",
    "FROM\n",
    "  ML.EXPLAIN_PREDICT(MODEL {BQ_DATASET_NAME}.{BQML_MODEL_NAME},\n",
    "    (SELECT * FROM bqmlpublic.demo_ga4churnprediction.training_data LIMIT 100)\n",
    "    ),\n",
    "  UNNEST(top_feature_attributions) as tfa\n",
    "WHERE\n",
    "  user_pseudo_id = \"7666337.2408476627\"\n",
    "\"\"\"\n",
    "\n",
    "print(sql_explain_predict)\n",
    "\n",
    "run_bq_query(sql_explain_predict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dc0c1c1b03f9"
   },
   "source": [
    "### Inspect the model on Vertex AI Model Registry"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0144d67a298e"
   },
   "source": [
    "When the model was trained in BigQuery ML, the line `model_registry=\"vertex_ai\"` registered the model to Vertex AI Model Registry automatically upon completion.\n",
    "\n",
    "You can view the model on the <a href=\"https://console.cloud.google.com/vertex-ai/models\" target=\"_blank\">Vertex AI Model Registry page</a>:\n",
    "\n",
    "![Model.png]()\n",
    "\n",
    "Or use the code below to check that it was successfully registered:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "id": "b00664302839"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: \"projects/233098494272/locations/us-central1/models/bqml_model_churn_9amu5eqp\"\n",
      "display_name: \"bqml_model_churn_9amu5eqp\"\n",
      "supported_deployment_resources_types: DEDICATED_RESOURCES\n",
      "create_time {\n",
      "  seconds: 1662981296\n",
      "  nanos: 754027000\n",
      "}\n",
      "update_time {\n",
      "  seconds: 1662981320\n",
      "  nanos: 179210000\n",
      "}\n",
      "etag: \"AMEw9yP80B8hGpRh4qoev0Z8qSdPdnlnDBwyGLE426vMfFFCNOKJjHutCCLZtZJz-Kw=\"\n",
      "version_id: \"1\"\n",
      "version_aliases: \"logistic_reg\"\n",
      "version_aliases: \"experimental\"\n",
      "version_aliases: \"default\"\n",
      "version_create_time {\n",
      "  seconds: 1662981296\n",
      "  nanos: 754027000\n",
      "}\n",
      "version_update_time {\n",
      "  seconds: 1662981320\n",
      "  nanos: 148137000\n",
      "}\n",
      "\n"
     ]
    }
   ],
   "source": [
    "model = vertex_ai.Model(model_name=BQML_MODEL_NAME)\n",
    "\n",
    "print(model.gca_resource)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "89455f708f54"
   },
   "source": [
    "### Deploy the model to an endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "b6120dcc1ff6"
   },
   "source": [
    "While BigQuery ML supports batch prediction with <a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict\" target=\"_blank\">ML.PREDICT</a> and <a href=\"https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-explain-predict\" target=\"_blank\">ML.EXPLAIN_PREDICT</a>, BigQuery ML is not suitable for real-time predictions where you need low latency predictions with potentially high frequency of requests.\n",
    "\n",
    "In other words, deploying the BigQuery ML model to an endpoint enables you to do online predictions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "e1ab7e1ac83c"
   },
   "source": [
    "#### Create a Vertex AI endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0a61ea55f685"
   },
   "source": [
    "To deploy your model to an endpoint, you will first need to create an endpoint before you deploy the model to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "id": "b7d80941cf30"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating Endpoint\n",
      "Create Endpoint backing LRO: projects/233098494272/locations/us-central1/endpoints/7731827339171987456/operations/4227433436396126208\n",
      "Endpoint created. Resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n",
      "To use this Endpoint in another session:\n",
      "endpoint = aiplatform.Endpoint('projects/233098494272/locations/us-central1/endpoints/7731827339171987456')\n",
      "bqml_model_churn_9amu5eqp-endpoint\n",
      "projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n"
     ]
    }
   ],
   "source": [
    "# TODO 3\n",
    "ENDPOINT_NAME = f\"{BQML_MODEL_NAME}-endpoint\"\n",
    "\n",
    "# Create a Vertex AI endpoint\n",
    "endpoint = vertex_ai.Endpoint.create(\n",
    "    display_name=ENDPOINT_NAME,\n",
    "    project=PROJECT_ID,\n",
    "    location=REGION,\n",
    ")\n",
    "\n",
    "print(endpoint.display_name)\n",
    "print(endpoint.resource_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "b58a104207d2"
   },
   "source": [
    "You can view your endpoints on the <a href=\"https://console.cloud.google.com/vertex-ai/endpoints\" target=\"_blank\">Vertex AI Endpoints page</a>:\n",
    "\n",
    "![Endpoint.png]()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "b58a104207d2"
   },
   "source": [
    "#### List endpoints"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "951ed1693f6b"
   },
   "source": [
    "List the endpoints to make sure it has successfully been created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "id": "c4d7707910bc"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<google.cloud.aiplatform.models.Endpoint object at 0x7fdadb722290> \n",
       " resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "endpoint.list()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ba0d40b26cfb"
   },
   "source": [
    "#### Deploy model to Vertex endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6a90be5b77a2"
   },
   "source": [
    "With the new endpoint, you can now deploy your model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "c70ecc568ee5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deploying model to Endpoint : projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n",
      "Using default machine_type: n1-standard-2\n",
      "Deploy Endpoint model backing LRO: projects/233098494272/locations/us-central1/endpoints/7731827339171987456/operations/1073787797329936384\n",
      "Endpoint model deployed. Resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.aiplatform.models.Endpoint object at 0x7fdadb718fd0> \n",
       "resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO 4\n",
    "# Deploy your model to the endpoint\n",
    "# deploying the model to the endpoint may take 10-15 minutes\n",
    "model.deploy(endpoint=endpoint)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "c303d779477b"
   },
   "source": [
    "You can also check on the status of your model by visiting the <a href=\"https://console.cloud.google.com/vertex-ai/endpoints\" target=\"_blank\">Vertex AI Endpoints page</a>."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4cb04d49c6f1"
   },
   "source": [
    "### Make online predictions to the endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2a888784de7b"
   },
   "source": [
    "Using a sample of the training data, you can test the endpoint to make online predictions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "id": "f17b049a846c"
   },
   "outputs": [],
   "source": [
    "df_sample_requests_list = [\n",
    "    {\n",
    "        \"country\": \"Turkey\",\n",
    "        \"operating_system\": \"Web\",\n",
    "        \"language\": \"None\",\n",
    "        \"cnt_user_engagement\": 28,\n",
    "        \"cnt_page_view\": 37,\n",
    "        \"cnt_view_item\": 6,\n",
    "        \"cnt_view_promotion\": 15,\n",
    "        \"cnt_select_promotion\": 4,\n",
    "        \"cnt_add_to_cart\": 0,\n",
    "        \"cnt_begin_checkout\": 0,\n",
    "        \"cnt_add_shipping_info\": 0,\n",
    "        \"cnt_add_payment_info\": 0,\n",
    "        \"cnt_purchase\": 0,\n",
    "        \"month\": 1,\n",
    "        \"julianday\": 1,\n",
    "        \"dayofweek\": 6,\n",
    "    },\n",
    "    {\n",
    "        \"country\": \"Macao\",\n",
    "        \"operating_system\": \"Web\",\n",
    "        \"language\": \"None\",\n",
    "        \"cnt_user_engagement\": 2,\n",
    "        \"cnt_page_view\": 4,\n",
    "        \"cnt_view_item\": 0,\n",
    "        \"cnt_view_promotion\": 0,\n",
    "        \"cnt_select_promotion\": 0,\n",
    "        \"cnt_add_to_cart\": 0,\n",
    "        \"cnt_begin_checkout\": 0,\n",
    "        \"cnt_add_shipping_info\": 0,\n",
    "        \"cnt_add_payment_info\": 0,\n",
    "        \"cnt_purchase\": 0,\n",
    "        \"month\": 1,\n",
    "        \"julianday\": 16,\n",
    "        \"dayofweek\": 7,\n",
    "    },\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "id": "b4839f31d2f8"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Prediction(predictions=[{'churned_values': ['1', '0'], 'churned_probs': [0.7541578275932675, 0.2458421724067325], 'predicted_churned': ['1']}, {'churned_values': ['1', '0'], 'churned_probs': [0.4827901063200363, 0.5172098936799636], 'predicted_churned': ['0']}], deployed_model_id='9057746003293110272', model_version_id='1', model_resource_name='projects/233098494272/locations/us-central1/models/bqml_model_churn_9amu5eqp', explanations=None)\n"
     ]
    }
   ],
   "source": [
    "# TODO 5\n",
    "# Make the prediction to the endpoint\n",
    "prediction = endpoint.predict(df_sample_requests_list)\n",
    "print(prediction)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "347ffda86396"
   },
   "source": [
    "You can then extract the predictions from the prediction response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "id": "ea659b7c178d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'churned_values': ['1', '0'],\n",
       "  'churned_probs': [0.7541578275932675, 0.2458421724067325],\n",
       "  'predicted_churned': ['1']},\n",
       " {'churned_values': ['1', '0'],\n",
       "  'churned_probs': [0.4827901063200363, 0.5172098936799636],\n",
       "  'predicted_churned': ['0']}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prediction.predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TpV-iwP9qw9c"
   },
   "source": [
    "## Cleaning up\n",
    "\n",
    "To clean up all Google Cloud resources used in this project, you can <a href=\"https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects\" target=\"_blank\">delete the Google Cloud\n",
    "project</a> you used for the tutorial.\n",
    "\n",
    "Otherwise, you can delete the individual resources you created in this tutorial:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "id": "sx_vKniMq9ZX"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Undeploying Endpoint model: projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n",
      "Undeploy Endpoint model backing LRO: projects/233098494272/locations/us-central1/endpoints/7731827339171987456/operations/6332866262191833088\n",
      "Endpoint model undeployed. Resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n",
      "Deleting Endpoint : projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n",
      "Delete Endpoint  backing LRO: projects/233098494272/locations/us-central1/operations/3785517722960396288\n",
      "Endpoint deleted. . Resource name: projects/233098494272/locations/us-central1/endpoints/7731827339171987456\n"
     ]
    }
   ],
   "source": [
    "# Undeploy model from endpoint and delete endpoint\n",
    "endpoint.undeploy_all()\n",
    "endpoint.delete()\n",
    "\n",
    "# Delete BigQuery dataset, including the BigQuery ML model\n",
    "! bq rm -r -f $PROJECT_ID:$BQ_DATASET_NAME"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "bqml-online-prediction.ipynb",
   "toc_visible": true
  },
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-6.m95",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-6:m95"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
